package com.library.dao;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Connection;

public class MySQLLibrarianDAO implements LibrarianDAO {
	private Connection conn;
	@Override
	public boolean insertLibrarian(Librarian librarian) {
		Librarian user = findLibrarian(librarian.getLibrarianId());
		conn = MySQLConnection.getConnection();
		boolean res = false;
		if ((user == null) && (conn != null)){
			String querry = "INSERT INTO librarians (userid,password,email,name,address,phone) VALUES('"
					+ librarian.getLibrarianId()
					+ "','"
					+ librarian.getPassword()
					+ "','"
					+ librarian.getEmail()
					+ "','"
					+ librarian.getName()
					+ "','"
					+ librarian.getAddress() + "','" + librarian.getPhone() + "')";
			
			try {
				Statement stmt;
				stmt = conn.createStatement();
				stmt.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;
			} finally{
				MySQLConnection.closeConnection(conn);
			}		
		}

		return res;
	}

	@Override
	public boolean removeLibrarian(int id) {
		conn = MySQLConnection.getConnection();
		boolean res = false;
		String querry = "DELETE FROM librarians WHERE (userid = '"+id+"')";
		Statement st;
		if (conn != null){
			try {
				st = conn.createStatement();
				st.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;		
			} finally{
				MySQLConnection.closeConnection(conn);
			}			
		}
		
		return res;
	}

	@Override
	public boolean updateLibrarian(Librarian librarian) {
		conn = MySQLConnection.getConnection();
		removeLibrarian(librarian.getLibrarianId());
		boolean kq = false;
		if (insertLibrarian(librarian))
			kq = true;
		MySQLConnection.closeConnection(conn);;
		return kq;
	}

	@Override
	public Librarian findLibrarian(int id) {
		conn = MySQLConnection.getConnection();
		Librarian res = null;
		if (conn != null){
			String querry = "SELECT * FROM librarians WHERE userid = '"+id+"'";
			try {
				Statement stmt = conn.createStatement() ;
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					res = new Librarian();
					res.setAddress(result.getString("address"));
					res.setPassword(result.getString("password"));
					res.setEmail(result.getString("email"));
					res.setPhone(result.getString("phone"));
					res.setName(result.getString("name"));
					res.setUserId(id);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
			
		}
		return res;
	}
}
